﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Diagnostics;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.Util;
using NPOI.POIFS.FileSystem;
using NPOI.HPSF;

public partial class UC_ucXH_LoaiPG : System.Web.UI.UserControl
{
    SqlParameter[] p;
    clsXuLy ex = new clsXuLy();
    NpoiExport cl = new NpoiExport();
    string frmName = "frmBaoCaoXuatHang";
    static int f_SuDung = 0, f_XemTatCa = 0, f_InBaoCao = 0;
    public string Header = System.Configuration.ConfigurationSettings.AppSettings["Header"].ToString();
    static int PageNumber = 10;
    static int CurrentPage = 1;
    static double kltx = 0, klban = 0, da12 = 0, da24 = 0, cat = 0, ximang = 0, nuoc = 0, phugia = 0, ttda12 = 0, ttda24 = 0, ttcat = 0, ttnuoc = 0, ttximang = 0, ttphugia = 0, ttvlth = 0, hotrocuoc = 0;
    static string ncc = "";
    static System.Data.DataTable dt;
    static System.Data.DataTable dtPrint;

    protected void Page_Load(object sender, EventArgs e)
    {
        if (string.IsNullOrEmpty(Convert.ToString(Session["user"])) || string.IsNullOrEmpty(Convert.ToString(Session["IDUser"])))
            Response.Redirect("Default.aspx");
        if (!IsPostBack)
        {
            string idnguoidung = Session["IDUser"].ToString();
            string sql = "select SuDung,Them,Sua,Xoa,InBaoCao,XemTatCa from XDAPhanQuyen where IDnguoidung='" + idnguoidung + "' and tag='" + frmName + "'";

            System.Data.DataTable dt2 = ex.GetData_Text(sql);
            if (dt2.Rows.Count > 0)
            {
                f_SuDung = int.Parse(dt2.Rows[0]["SuDung"].ToString());
                f_InBaoCao = int.Parse(dt2.Rows[0]["InBaoCao"].ToString());
                f_XemTatCa = int.Parse(dt2.Rows[0]["XemTatCa"].ToString());
            }


            CurrentPage = 1;
            for (int i = 1; i <= 12; i++)
            {
                dlThang.Items.Add(new ListItem("Tháng " + i.ToString(), i.ToString()));
            }
            dlThang.Items.Insert(0, new ListItem("Theo thời gian", ""));
            dlThang.SelectedIndex = 0;

            int year1 = DateTime.Now.Year - 3;
            int year2 = DateTime.Now.Year;
            for (int i = year2; i >= year1; i--)
            {
                dlNam.Items.Add(new ListItem("Năm " + i.ToString(), i.ToString()));
            }
            dlNam.SelectedValue = year2.ToString();

            txtTuNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            txtDenNgay.Text = DateTime.Now.ToString("dd/MM/yyyy");
            dlNam.Visible = false;

            LoadLoaiPG(dlLoaiPhuGia);
            LoadTramTron(dlTramTron);
        }
    }
    void LoadTramTron(DropDownList dlTramTron)
    {
        dlTramTron.Items.Clear();
        dlTramTron.DataSource = ex.GetData("sp_xdanhaphang_ListTramTron");
        dlTramTron.DataBind();
        dlTramTron.Items.Insert(0, new ListItem("--Chọn trạm trộn--", ""));
        dlTramTron.SelectedIndex = 0;
    }
    void LoadLoaiPG(DropDownList dlPhuGia)
    {
        dlPhuGia.Items.Clear();
        dlPhuGia.Items.Insert(0, new ListItem("--Chọn loại phụ gia--", ""));
        dlPhuGia.SelectedIndex = 0;
    }

    protected void dlLoaiBaoCao_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlLoaiBaoCao.SelectedIndex.Equals(0))
            Response.Redirect("BCXHTongHop.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(1))
            Response.Redirect("BCXHBienSoXe.aspx");
        else if (dlLoaiBaoCao.SelectedIndex.Equals(2))
            Response.Redirect("BCXHMacHopDong.aspx");
        else
            if (dlLoaiBaoCao.SelectedIndex.Equals(3))
                Response.Redirect("BCXHMacThucXuat.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(4))
                Response.Redirect("BCXHLaiXe.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(5))
                Response.Redirect("BCXHLoaiPhuGia.aspx");
            else if (dlLoaiBaoCao.SelectedIndex.Equals(6))
                Response.Redirect("BCXHKhachHang.aspx");
    }
    protected void btnXem_Click(object sender, EventArgs e)
    {
        if (f_SuDung.Equals(1))
        {
            CurrentPage = 1;
            btnNext.Enabled = true;
            Search(CurrentPage);
        }
        else
            gstGetMess("Bạn không có quyền xem báo cáo này", "");
    }
    void Search(int page)
    {
        int index = 2;
        if (dlLoaiPhuGia.SelectedIndex.Equals(0))
            index = 2;
        else if (dlLoaiPhuGia.SelectedIndex.Equals(1))
            index = 3;
        else if (dlLoaiPhuGia.SelectedIndex > 1)
            index = 1;


        getDate();
        p = new SqlParameter[6];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;
        p[2] = new SqlParameter("@LoaiPG", ex.GetGuid(dlLoaiPhuGia.SelectedValue));
        p[2].SqlDbType = SqlDbType.UniqueIdentifier;

        p[3] = new SqlParameter("@Index", index);
        p[3].SqlDbType = SqlDbType.Int;
        p[4] = new SqlParameter("@PageNumber", page);
        p[4].SqlDbType = SqlDbType.Int;
        p[5] = new SqlParameter("@RowspPage", PageNumber);
        p[5].SqlDbType = SqlDbType.Int;

        GV.DataSource = ex.GetData("sp_BCXH_LoaiPG", p);
        GV.DataBind();

        ////get footer
        //p = new SqlParameter[4];
        //p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        //p[0].SqlDbType = SqlDbType.SmallDateTime;
        //p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        //p[1].SqlDbType = SqlDbType.SmallDateTime;
        //p[2] = new SqlParameter("@LoaiPG", ex.GetGuid(dlLoaiPhuGia.SelectedValue));
        //p[2].SqlDbType = SqlDbType.UniqueIdentifier;
        //p[3] = new SqlParameter("@Index", index);
        //p[3].SqlDbType = SqlDbType.Int;

        //if (GV.Rows.Count > 0)
        //{
        //    dt = ex.GetData("sp_BCXH_BSX_GetFooter", p);
        //    if (dt.Rows.Count > 0)
        //    {
        //        kltx = double.Parse(dt.Rows[0]["KLThucXuat"].ToString());
        //        klban = double.Parse(dt.Rows[0]["KLBan"].ToString());
        //        da12 = double.Parse(dt.Rows[0]["Da12"].ToString());
        //        da24 = double.Parse(dt.Rows[0]["Da24"].ToString());
        //        cat = double.Parse(dt.Rows[0]["CatVang"].ToString());
        //        ximang = double.Parse(dt.Rows[0]["XiMang"].ToString());
        //        nuoc = double.Parse(dt.Rows[0]["Nuoc"].ToString());
        //        phugia = double.Parse(dt.Rows[0]["PhuGia"].ToString());
        //        ttda12 = double.Parse(dt.Rows[0]["TTDGDa12"].ToString());
        //        ttda24 = double.Parse(dt.Rows[0]["TTDGDa24"].ToString());
        //        ttcat = double.Parse(dt.Rows[0]["TTDGCatVang"].ToString());
        //        ttximang = double.Parse(dt.Rows[0]["TTDGXiMang"].ToString());
        //        ttnuoc = double.Parse(dt.Rows[0]["TTDGNuoc"].ToString());
        //        ttphugia = double.Parse(dt.Rows[0]["TTDGPhuGia"].ToString());
        //        ttvlth = double.Parse(dt.Rows[0]["TTVLTH"].ToString());
        //        hotrocuoc = double.Parse(dt.Rows[0]["TienHoTroCuoc"].ToString());


        //        GV.FooterRow.Cells[7].Text = "Tổng cộng";
        //        GV.FooterRow.Cells[7].HorizontalAlign = HorizontalAlign.Center;

        //        GV.FooterRow.Cells[11].Text = ConvertDecimal(kltx);
        //        GV.FooterRow.Cells[12].Text = ConvertDecimal(klban);
        //        GV.FooterRow.Cells[15].Text = ConvertDecimal(da12);
        //        GV.FooterRow.Cells[16].Text = ConvertDecimal(da24);
        //        GV.FooterRow.Cells[17].Text = ConvertDecimal(cat);
        //        GV.FooterRow.Cells[18].Text = ConvertDecimal(ximang);
        //        GV.FooterRow.Cells[19].Text = ConvertDecimal(nuoc);
        //        GV.FooterRow.Cells[20].Text = ConvertDecimal(phugia);

        //        GV.FooterRow.Cells[27].Text = ConvertDecimal(ttda12);
        //        GV.FooterRow.Cells[28].Text = ConvertDecimal(ttda24);
        //        GV.FooterRow.Cells[29].Text = ConvertDecimal(ttcat);
        //        GV.FooterRow.Cells[30].Text = ConvertDecimal(ttximang);
        //        GV.FooterRow.Cells[31].Text = ConvertDecimal(ttnuoc);
        //        GV.FooterRow.Cells[32].Text = ConvertDecimal(ttphugia);

        //        GV.FooterRow.Cells[33].Text = ConvertDecimal(ttvlth);
        //        GV.FooterRow.Cells[34].Text = ConvertDecimal(hotrocuoc);
        //    }
        //}
    }
    string ConvertDecimal(double s)
    {
        string q = "";
        if (s.ToString().Contains("."))
        {
            double x = double.Parse(s.ToString().Substring(0, s.ToString().IndexOf('.')));
            q = (x != 0) ? string.Format("{0:0,0}", x) : x.ToString();
            q += s.ToString().Substring(s.ToString().IndexOf('.'));
        }
        else
            q = (s != 0) ? string.Format("{0:0,0}", s) : s.ToString();
        return q;
    }
    protected void btnPrint_Click(object sender, EventArgs e)
    {
        if (f_InBaoCao.Equals(1))
        {
            try
            {
                int index = 2;
                if (dlLoaiPhuGia.SelectedIndex.Equals(0))
                    index = 2;
                else if (dlLoaiPhuGia.SelectedIndex.Equals(1))
                    index = 3;
                else if (dlLoaiPhuGia.SelectedIndex > 1)
                    index = 1;



                getDate();
                p = new SqlParameter[4];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@LoaiPG", ex.GetGuid(dlLoaiPhuGia.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@Index", index);
                p[3].SqlDbType = SqlDbType.Int;

                dtPrint = ex.GetData("sp_BCXH_LoaiPG_Print1", p);
                PrintNPOI(dtPrint);
            }
            catch (Exception ax)
            {
                gstGetMess(ax.Message, "");

            }
        }
        else
            gstGetMess("Bạn không có quyền in báo cáo này", "");
    }
    void PrintNPOI(System.Data.DataTable dt)
    {
        var workbook = new HSSFWorkbook();

        if (dt.Rows.Count > 0)
        {
            string sheetname = "Bảng tổng hợp";

            if (dlLoaiPhuGia.SelectedIndex > 0)
                sheetname = dlLoaiPhuGia.SelectedItem.Text;
            else sheetname = "Bảng tổng hợp";

            var sheet = workbook.CreateSheet(sheetname);

            #region CSS
            var headerLabelCellStyle = workbook.CreateCellStyle();
            headerLabelCellStyle.Alignment = HorizontalAlignment.LEFT;
            headerLabelCellStyle.BorderBottom = CellBorderType.THIN;
            headerLabelCellStyle.BorderLeft = CellBorderType.THIN;
            headerLabelCellStyle.BorderRight = CellBorderType.THIN;
            headerLabelCellStyle.BorderTop = CellBorderType.THIN;

            var headerLabelFont = workbook.CreateFont();
            headerLabelFont.FontName = "Times New Roman";

            headerLabelFont.FontHeightInPoints = 11;
            headerLabelFont.Color = HSSFColor.BLACK.index;
            headerLabelCellStyle.SetFont(headerLabelFont);

            //--------------------------

            var hsRight = workbook.CreateCellStyle();
            hsRight.Alignment = HorizontalAlignment.RIGHT;
            hsRight.BorderBottom = CellBorderType.THIN;
            hsRight.BorderLeft = CellBorderType.THIN;
            hsRight.BorderRight = CellBorderType.THIN;
            hsRight.BorderTop = CellBorderType.THIN;

            hsRight.SetFont(headerLabelFont);
            //-------------------
            var hsCenter = workbook.CreateCellStyle();
            hsCenter.Alignment = HorizontalAlignment.CENTER;
            hsCenter.BorderBottom = CellBorderType.THIN;
            hsCenter.BorderLeft = CellBorderType.THIN;
            hsCenter.BorderRight = CellBorderType.THIN;
            hsCenter.BorderTop = CellBorderType.THIN;

            hsCenter.SetFont(headerLabelFont);
            //-----------------------

            var hs1 = workbook.CreateCellStyle();
            hs1.Alignment = HorizontalAlignment.LEFT;

            var hsb = workbook.CreateFont();
            hsb.Boldweight = (short)FontBoldWeight.BOLD;
            hsb.FontName = "Times New Roman";

            hsb.FontHeightInPoints = 11;
            hsb.Color = HSSFColor.BLACK.index;
            hs1.SetFont(hsb);

            //-------------
            var hs2 = workbook.CreateCellStyle();
            hs2.Alignment = HorizontalAlignment.CENTER;

            var hsb2 = workbook.CreateFont();
            hsb2.Boldweight = (short)FontBoldWeight.BOLD;
            hsb2.FontName = "Times New Roman";

            hsb2.FontHeightInPoints = 18;
            hsb2.Color = HSSFColor.BLACK.index;
            hs2.SetFont(hsb2);
            //------------------------------
            var hs3 = workbook.CreateCellStyle();
            hs3.Alignment = HorizontalAlignment.CENTER;
            hs3.BorderBottom = CellBorderType.THIN;
            var hsb3 = workbook.CreateFont();
            hsb3.Boldweight = (short)FontBoldWeight.BOLD;
            hsb3.FontName = "Times New Roman";

            hsb3.FontHeightInPoints = 14;
            hsb3.Color = HSSFColor.BLACK.index;
            hs3.SetFont(hsb3);

            //----------------------------
            var hs4 = workbook.CreateCellStyle();
            hs4.Alignment = HorizontalAlignment.CENTER;
            hs4.BorderBottom = CellBorderType.THIN;
            hs4.BorderLeft = CellBorderType.THIN;
            hs4.BorderRight = CellBorderType.THIN;
            hs4.BorderTop = CellBorderType.THIN;

            var hsb4 = workbook.CreateFont();
            hsb4.Boldweight = (short)FontBoldWeight.BOLD;
            hsb4.FontName = "Times New Roman";

            hsb4.FontHeightInPoints = 11;
            hsb4.Color = HSSFColor.BLACK.index;
            hs4.SetFont(hsb4);

            #endregion

            //Start header-----------------------------------------------------------------------
            #region Header
            //tao hnag dau tien
            var rowIndex = 0;

            //cong ty
            var row = sheet.CreateRow(rowIndex);
            Cell r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            CellRangeAddress cra = new CellRangeAddress(0, 0, 0, 4);
            sheet.AddMergedRegion(cra);

            //chi nhanh
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
            r1c1.CellStyle = hs1;
            r1c1.Row.Height = 400;

            cra = new CellRangeAddress(1, 1, 0, 4);
            sheet.AddMergedRegion(cra);

            //tieu de bao cao
            string ngaythang = dlThang.SelectedIndex > 0 ? dlThang.SelectedItem.Text.ToUpper() + " " + dlNam.SelectedItem.Text.ToUpper() : "TỪ " + txtTuNgay.Text + " ĐẾN " + txtDenNgay.Text;
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue("TỔNG HỢP SẢN LƯỢNG BÁN HÀNG THEO LOẠI PHỤ GIA " + ngaythang);
            r1c1.CellStyle = hs2;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(2, 2, 0, 12);
            sheet.AddMergedRegion(cra);

            //loai phu gia
            string bs = dlLoaiPhuGia.SelectedIndex > 0 ? "Loại phụ gia: " + dlLoaiPhuGia.SelectedItem.Text : "";
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            r1c1 = row.CreateCell(0);
            r1c1.SetCellValue(bs);
            r1c1.CellStyle = hs3;
            r1c1.Row.Height = 500;

            cra = new CellRangeAddress(3, 3, 0, 12);
            sheet.AddMergedRegion(cra);

            //freeze panes
            sheet.CreateFreezePane(0, 6);
            #endregion

            #region Header1-2
            rowIndex++;
            //header1
            row = sheet.CreateRow(rowIndex);
            string[] header1 = { "Vật liệu xuất theo phiếu", "Đơn giá vật liệu theo phiếu", "Thành tiền vật liệu theo phiếu" };
            int[] me = { 15, 21, 27 };

            //tao header1
            for (int m = 0; m <= 2; m++)
            {

                r1c1 = row.CreateCell(me[m]);
                r1c1.SetCellValue(header1[m]);
                r1c1.CellStyle = hs4;
                r1c1.Row.Height = 400;

                cra = new CellRangeAddress(4, 4, me[m], me[m] + 5);
                sheet.AddMergedRegion(cra);

            }

            //fix lai border
            for (int h = 0; h <= 36; h++)
            {
                if (h != 15 && h != 21 && h != 27)
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }

            //header2
            rowIndex++;
            row = sheet.CreateRow(rowIndex);
            string[] header = { "Ngày xuất", "Số phiếu", "Biển số", "Lái xe", "Giờ xuất", "Ca làm việc", "Khách hàng", "Độ sụt", "Mác TX", "Mác HĐ", "KL TX", "KL bán", "Loại đá", "Loại PG", "Đá 1*2", "Đá 2*4", "Cát", "Xi măng", "Nước", "Phụ gia", "Đá 1*2", "Đá 2*4", "Cát", "Xi măng", "Nước", "Phụ gia", "Đá 1*2", "Đá 2*4", "Cát", "Xi măng", "Nước", "Phụ gia", "TT VLTH", "Hỗ trợ cước", "Loại ca bơm", "NVKD" };

            var cell = row.CreateCell(0);
            cell.SetCellValue("STT");
            cell.CellStyle = hs4;
            cell.Row.Height = 400;


            //tao tieu de cot
            for (int j = 1; j <= header.Length; j++)
            {
                cell = row.CreateCell(j);
                cell.SetCellValue(header[j - 1].ToString());
                cell.CellStyle = hs4;
                cell.Row.Height = 500;
            }
            #endregion
            //End header-----------------------------------------------------------------------

            //ghi du lieu tung dong
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                rowIndex++;
                row = sheet.CreateRow(rowIndex);

                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    cell = row.CreateCell(j);
                    if (j == 11 || j == 12 || (j >= 15 && j <= 34))
                    {
                        if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                        {
                            cell.SetCellType(CellType.NUMERIC);
                            cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                        }
                        cell.CellStyle = hsRight;
                    }
                    else
                    {
                        cell.SetCellValue(dt.Rows[i][j].ToString());

                        if (j <= 3 || (j >= 5 && j <= 6) || (j >= 8 && j <= 10))
                            cell.CellStyle = hsCenter;
                        else
                            cell.CellStyle = headerLabelCellStyle;
                    }
                    cell.Row.Height = 500;
                }

            }

            #region Footer
            //-----------Footer
            string[] footer = { "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI" };
            rowIndex++;
            row = sheet.CreateRow(rowIndex);

            cell = row.CreateCell(7);
            cell.SetCellValue("Tổng cộng");
            cell.Row.Height = 500;
            cell.CellStyle = hs4;

            for (int k = 11; k <= 34; k++)
            {
                if (k != 13 && k != 14 && k != 21 && k != 22 && k != 23 && k != 24 && k != 25 && k != 26)
                {
                    cell = row.CreateCell(k);
                    cell.CellFormula = "Sum(" + footer[k - 11] + "7:" + footer[k - 11] + rowIndex.ToString() + ")";
                    cell.Row.Height = 500;
                    cell.CellStyle = hs4;
                }
            }

            //fix lai border
            for (int h = 0; h <= 36; h++)
            {
                if (h <= 6 || h == 8 || h == 9 || h == 10 || h == 13 || h == 14 || h == 21 || h == 22 || h == 23 || h == 24 || h == 25 || h == 26 || h == 35 || h == 36)
                {
                    r1c1 = row.CreateCell(h);
                    r1c1.CellStyle = hs4;
                    r1c1.Row.Height = 400;
                }
            }
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }
            #endregion

            //end sheet tong hop--------------------------

            //in tung loai phu gia vao tung sheet
            #region LoaiPhuGia
            string loaiphugia = "ko", lpg = "";
            if (dlLoaiPhuGia.SelectedIndex.Equals(0))
            {
                int index = 2;
                if (dlLoaiPhuGia.SelectedIndex.Equals(0))
                    index = 2;
                else if (dlLoaiPhuGia.SelectedIndex.Equals(1))
                    index = 3;
                else if (dlLoaiPhuGia.SelectedIndex > 1)
                    index = 1;



                getDate();
                p = new SqlParameter[4];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;
                p[2] = new SqlParameter("@LoaiPG", ex.GetGuid(dlLoaiPhuGia.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@Index", index);
                p[3].SqlDbType = SqlDbType.Int;

                dt = ex.GetData("sp_BCXH_LoaiPG_Print", p);
                int STT = 0;
                //ghi sheet con------------------------------------------
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    STT++;
                    #region Start
                    if (i > 0)
                    {
                        loaiphugia = dt.Rows[i]["LoaiPhuGia"].ToString();

                        if (!loaiphugia.Equals(dt.Rows[i - 1]["LoaiPhuGia"].ToString()))
                        {
                            STT = 0;
                            //-----------Footer
                            #region Footer

                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);

                            cell = row.CreateCell(2);
                            cell.SetCellValue("Tổng cộng");
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            cell = row.CreateCell(4);
                            cell.CellFormula = "Sum(E7:" + "E" + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            cell = row.CreateCell(7);
                            cell.CellFormula = "Sum(H7:" + "H" + rowIndex.ToString() + ")";
                            cell.Row.Height = 500;
                            cell.CellStyle = hs4;

                            //fix lai border
                            for (int h = 0; h <= 6; h++)
                            {
                                if (h != 2 && h != 4)
                                {
                                    r1c1 = row.CreateCell(h);
                                    r1c1.CellStyle = hs4;
                                    r1c1.Row.Height = 400;
                                }
                            }
                            for (int ik = 0; ik <= dt.Columns.Count; ik++)
                            {
                                sheet.AutoSizeColumn(ik);
                            }
                            #endregion

                            //---End Footer

                            sheet = workbook.CreateSheet(cl.EscapeSheetName(loaiphugia.Trim().Equals("") ? "Không dùng phụ gia" : loaiphugia));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP SL BÁN HÀNG THEO LOẠI PHỤ GIA " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 6);
                            sheet.AddMergedRegion(cra);

                            //loai phu gia
                            lpg = loaiphugia.Trim().Equals("") ? "Không dùng phụ gia" : loaiphugia;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Loại phụ gia: " + lpg);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 6);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header

                            rowIndex++;
                            rowIndex++;
                            //header1
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            string[] header2 = { "STT", "Ngày xuất", "Khách hàng", "Mác TX", "KL thực xuất", "Loại đá", "Loại phụ gia", "Số lít" };
                            for (int m = 0; m <= 7; m++)
                            {

                                r1c1 = row.CreateCell(m);
                                r1c1.SetCellValue(header2[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                            }
                            #endregion
                            STT++;

                        }
                    }
                    else
                    {
                        loaiphugia = "ko";
                        if (!loaiphugia.Equals(dt.Rows[i]["LoaiPhuGia"].ToString()))
                        {
                            loaiphugia = dt.Rows[i]["LoaiPhuGia"].ToString();

                            sheet = workbook.CreateSheet(cl.EscapeSheetName(loaiphugia.Trim().Equals("") ? "Không dùng phụ gia" : loaiphugia));
                            rowIndex = 0;

                            //Start header-----------------------------------------------------------------------
                            #region Header
                            //tao hnag dau tien

                            //cong ty
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(0, 0, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //chi nhanh
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                            r1c1.CellStyle = hs1;
                            r1c1.Row.Height = 400;

                            cra = new CellRangeAddress(1, 1, 0, 4);
                            sheet.AddMergedRegion(cra);

                            //tieu de bao cao
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("TỔNG HỢP SL BÁN HÀNG THEO LOẠI PHỤ GIA " + ngaythang);
                            r1c1.CellStyle = hs2;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(2, 2, 0, 6);
                            sheet.AddMergedRegion(cra);

                            //loai phu gia
                            lpg = loaiphugia.Trim().Equals("") ? "Không dùng phụ gia" : loaiphugia;
                            rowIndex++;
                            row = sheet.CreateRow(rowIndex);
                            r1c1 = row.CreateCell(0);
                            r1c1.SetCellValue("Loại phụ gia: " + lpg);
                            r1c1.CellStyle = hs3;
                            r1c1.Row.Height = 500;

                            cra = new CellRangeAddress(3, 3, 0, 6);
                            sheet.AddMergedRegion(cra);

                            //freeze panes
                            sheet.CreateFreezePane(0, 6);
                            #endregion

                            #region Header
                            rowIndex++;
                            rowIndex++;
                            //header1
                            row = sheet.CreateRow(rowIndex);

                            //tao header1
                            string[] header2 = { "STT", "Ngày xuất", "Khách hàng", "Mác TX", "KL thực xuất", "Loại đá", "Loại phụ gia", "Số lít" };
                            for (int m = 0; m <= 7; m++)
                            {

                                r1c1 = row.CreateCell(m);
                                r1c1.SetCellValue(header2[m]);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;

                            }
                            #endregion
                        }
                        for (int ik = 0; ik <= dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion

                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    cell = row.CreateCell(0);
                    cell.SetCellValue((STT).ToString());
                    cell.CellStyle = hsCenter;

                    for (int j = 1; j < dt.Columns.Count; j++)
                    {
                        cell = row.CreateCell(j);
                        if (j == 4 || j == 7)
                        {
                            if (!dt.Rows[i][j].ToString().Trim().Equals(""))
                            {
                                cell.SetCellType(CellType.NUMERIC);
                                cell.SetCellValue(double.Parse(dt.Rows[i][j].ToString()));
                            }
                            cell.CellStyle = hsRight;
                        }
                        else
                        {
                            cell.SetCellValue(dt.Rows[i][j].ToString());

                            if (j == 1 || j == 3 || j == 5)
                                cell.CellStyle = hsCenter;
                            else
                                cell.CellStyle = headerLabelCellStyle;
                        }
                        cell.Row.Height = 500;
                    }

                    #region Footer
                    if (i == dt.Rows.Count - 1)
                    {
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        cell = row.CreateCell(2);
                        cell.SetCellValue("Tổng cộng");
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        cell = row.CreateCell(4);
                        cell.CellFormula = "Sum(E7:" + "E" + rowIndex.ToString() + ")";
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        cell = row.CreateCell(7);
                        cell.CellFormula = "Sum(H7:" + "H" + rowIndex.ToString() + ")";
                        cell.Row.Height = 500;
                        cell.CellStyle = hs4;

                        //fix lai border
                        for (int h = 0; h <= 6; h++)
                        {
                            if (h != 2 && h != 4)
                            {
                                r1c1 = row.CreateCell(h);
                                r1c1.CellStyle = hs4;
                                r1c1.Row.Height = 400;
                            }
                        }
                        for (int ik = 0; ik < dt.Columns.Count; ik++)
                        {
                            sheet.AutoSizeColumn(ik);
                        }
                    }
                    #endregion
                }
            }
            #endregion

            //in tong hop loai phu gia
            if (dlLoaiPhuGia.SelectedIndex != 1)
            {
                p = new SqlParameter[4];
                p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
                p[0].SqlDbType = SqlDbType.SmallDateTime;
                p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
                p[1].SqlDbType = SqlDbType.SmallDateTime;

                p[2] = new SqlParameter("@LoaiPG", ex.GetGuid(dlLoaiPhuGia.SelectedValue));
                p[2].SqlDbType = SqlDbType.UniqueIdentifier;
                p[3] = new SqlParameter("@Index", dlLoaiPhuGia.SelectedIndex > 1 ? 1 : 2);
                p[3].SqlDbType = SqlDbType.Int;

                var tonghop = ex.GetData("sp_BCXH_LoaiPG_THPhuGia", p);
                if (tonghop.Rows.Count > 0)
                {
                    sheet = workbook.CreateSheet(cl.EscapeSheetName("Tổng hợp phụ gia"));
                    rowIndex = 0;

                    //Start header-----------------------------------------------------------------------
                    #region Header
                    //tao hnag dau tien

                    //cong ty
                    row = sheet.CreateRow(rowIndex);
                    r1c1 = row.CreateCell(0);
                    r1c1.SetCellValue("Công ty cổ phần Xây Dựng Đức Anh");
                    r1c1.CellStyle = hs1;
                    r1c1.Row.Height = 400;

                    cra = new CellRangeAddress(0, 0, 0, 4);
                    sheet.AddMergedRegion(cra);

                    //chi nhanh
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    r1c1 = row.CreateCell(0);
                    r1c1.SetCellValue("Chi nhánh Trạm trộn Lâm Thao");
                    r1c1.CellStyle = hs1;
                    r1c1.Row.Height = 400;

                    cra = new CellRangeAddress(1, 1, 0, 4);
                    sheet.AddMergedRegion(cra);

                    //tieu de bao cao
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    r1c1 = row.CreateCell(0);
                    r1c1.SetCellValue("BẢNG TỔNG HỢP PHỤ GIA " + ngaythang);
                    r1c1.CellStyle = hs2;
                    r1c1.Row.Height = 500;

                    cra = new CellRangeAddress(2, 2, 0, 7);
                    sheet.AddMergedRegion(cra);


                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);
                    r1c1 = row.CreateCell(0);
                    r1c1.SetCellValue(dlLoaiPhuGia.SelectedIndex > 0 ? "Loại phụ gia: " + dlLoaiPhuGia.SelectedItem.Text : "");
                    r1c1.CellStyle = hs2;
                    r1c1.Row.Height = 500;

                    cra = new CellRangeAddress(3, 3, 0, 7);
                    sheet.AddMergedRegion(cra);

                    //freeze panes
                    sheet.CreateFreezePane(0, 6);
                    #endregion

                    #region Header
                    //header1
                    rowIndex++;
                    row = sheet.CreateRow(rowIndex);

                    string[] header3 = { "Khối lượng", "Đơn giá", "Thành tiền" };
                    for (int m = 1; m <= 3; m++)
                    {
                        r1c1 = row.CreateCell(m * 2);
                        r1c1.SetCellValue(header3[m - 1]);
                        r1c1.CellStyle = hs4;
                        r1c1.Row.Height = 400;

                        cra = new CellRangeAddress(4, 4, m * 2, m * 2 + 1);
                        sheet.AddMergedRegion(cra);
                    }

                    for (int h = 0; h <= 7; h++)
                    {
                        if (h == 0 || h % 2 != 0)
                        {
                            r1c1 = row.CreateCell(h);
                            r1c1.CellStyle = hs4;
                            r1c1.Row.Height = 400;
                        }
                    }

                    //header2
                    rowIndex++;

                    row = sheet.CreateRow(rowIndex);

                    string[] header4 = { "STT", "Tên phụ gia", "Có VAT", "Không VAT", "Có VAT", "Không VAT", "Có VAT", "Không VAT" };
                    for (int m = 0; m <= 7; m++)
                    {
                        r1c1 = row.CreateCell(m);
                        r1c1.SetCellValue(header4[m]);
                        r1c1.CellStyle = hs4;
                        r1c1.Row.Height = 400;

                    }
                    #endregion

                    for (int i = 0; i < tonghop.Rows.Count; i++)
                    {
                        rowIndex++;
                        row = sheet.CreateRow(rowIndex);

                        for (int j = 0; j < tonghop.Columns.Count; j++)
                        {
                            cell = row.CreateCell(j);
                            if (j >= 2)
                            {
                                if (!tonghop.Rows[i][j].ToString().Trim().Equals(""))
                                {
                                    cell.SetCellType(CellType.NUMERIC);
                                    cell.SetCellValue(double.Parse(tonghop.Rows[i][j].ToString()));
                                }
                                cell.CellStyle = hsRight;
                            }
                            else
                            {
                                cell.SetCellValue(tonghop.Rows[i][j].ToString());

                                if (j == 0)
                                    cell.CellStyle = hsCenter;
                                else
                                    cell.CellStyle = headerLabelCellStyle;
                            }
                            cell.Row.Height = 500;
                        }

                    }
                    for (int i = 0; i < tonghop.Columns.Count; i++)
                    {
                        sheet.AutoSizeColumn(i);
                    }
                }
            }
            using (var exportData = new MemoryStream())
            {
                workbook.Write(exportData);
                string filename = dlLoaiPhuGia.SelectedIndex > 0 ? dlLoaiPhuGia.SelectedItem.Text : "";
                string saveAsFileName = "BCXH-LoaiPG-" + filename + " " + ngaythang + ".xls";

                Response.ContentType = "application/vnd.ms-excel";
                Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", saveAsFileName));
                Response.Clear();
                Response.BinaryWrite(exportData.GetBuffer());
                Response.End();
            }
        }

    }
    private void gstGetMess(string gstMess, string gstLink)
    {
        if (gstLink == "")
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "')", true);
        else
            ScriptManager.RegisterClientScriptBlock(this, this.GetType(), " ", "alert('" + gstMess + "');window.location.href='" + gstLink + "'", true);

    }
    protected void dlThang_SelectedIndexChanged(object sender, EventArgs e)
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            txtTuNgay.Visible = true;
            txtDenNgay.Visible = true;
            dlNam.Visible = false;
        }
        else
        {
            txtTuNgay.Visible = false;
            txtDenNgay.Visible = false;
            dlNam.Visible = true;
        }

        getDate();

        p = new SqlParameter[2];
        p[0] = new SqlParameter("@TuNgay", ex.GetNgayThang(hdTuNgay.Value));
        p[0].SqlDbType = SqlDbType.SmallDateTime;
        p[1] = new SqlParameter("@DenNgay", ex.GetNgayThang(hdDenNgay.Value));
        p[1].SqlDbType = SqlDbType.SmallDateTime;

        dlLoaiPhuGia.Items.Clear();
        dlLoaiPhuGia.DataSource = ex.GetData("sp_BCXH_LoadPhuGia", p);
        dlLoaiPhuGia.DataBind();
        dlLoaiPhuGia.Items.Insert(0, new ListItem("--Chọn loại phụ gia--", ""));
        dlLoaiPhuGia.Items.Insert(1, new ListItem("Không dùng phụ gia", Guid.Empty.ToString()));
        dlLoaiPhuGia.SelectedIndex = 0;

    }
    public void getDate()
    {
        if (dlThang.SelectedIndex.Equals(0))
        {
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
        else
        {
            txtTuNgay.Text = "1/" + dlThang.SelectedValue + "/" + dlNam.SelectedValue;
            txtDenNgay.Text = GetLastDayOfMonth(int.Parse(dlThang.SelectedValue)).ToString("dd/MM/yyyy");
            hdTuNgay.Value = txtTuNgay.Text;
            hdDenNgay.Value = txtDenNgay.Text;
        }
    }
    public DateTime GetLastDayOfMonth(int iMonth)
    {
        DateTime dtResult = new DateTime(int.Parse(dlNam.SelectedValue), iMonth, 1);
        dtResult = dtResult.AddMonths(1);
        dtResult = dtResult.AddDays(-(dtResult.Day));
        return dtResult;
    }
    protected void GV_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == System.Web.UI.WebControls.DataControlRowType.DataRow)
        {
            e.Row.Attributes.Add("onmouseover", "this.originalstyle=this.style.backgroundColor;this.style.backgroundColor='#EEFFAA'");
            e.Row.Attributes.Add("onmouseout", "this.style.backgroundColor=this.originalstyle;");
        }
    }
    protected void GV_RowCreated(object sender, GridViewRowEventArgs e)
    {
        if (e.Row.RowType == DataControlRowType.Header)
        {
            GridView HeaderGrid = (GridView)sender;
            GridViewRow HeaderGridRow = new GridViewRow(0, 0, DataControlRowType.Header, DataControlRowState.Insert);
            HeaderGridRow.Height = 20;
            HeaderGridRow.HorizontalAlign = HorizontalAlign.Center;

            TableCell HeaderCell = new TableCell();

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 14;


            //vat lieu xuat theo phieu
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "Vật liệu xuất theo phiếu";
            HeaderCell.ColumnSpan = 6;

            //Don gia xuat theo phieu
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "Đơn giá vật liệu theo phiếu";
            HeaderCell.ColumnSpan = 6;

            //thnah tien vat lieu xuat theo phieu
            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "Thành tiền vật liệu xuất theo phiếu";
            HeaderCell.ColumnSpan = 6;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            HeaderGridRow.Cells.Add(HeaderCell);
            HeaderCell = new TableCell();
            HeaderCell.Text = "";
            HeaderCell.ColumnSpan = 1;

            GV.Controls[0].Controls.AddAt(0, HeaderGridRow);
        }
    }

    protected void btnFirst_Click(object sender, EventArgs e)
    {
        CurrentPage = 1;

        Search(CurrentPage);

        btnPre.Enabled = false;
        btnNext.Enabled = true;
        btnFirst.Enabled = false;
    }
    protected void btnPre_Click(object sender, EventArgs e)
    {
        if (CurrentPage > 1)
        {
            CurrentPage--;
            btnPre.Enabled = true;
            btnNext.Enabled = true;
            btnFirst.Enabled = true;

            Search(CurrentPage);
        }
        else
        {
            btnFirst.Enabled = false;
            btnPre.Enabled = false;
            btnNext.Enabled = true;
        }
    }
    protected void btnNext_Click(object sender, EventArgs e)
    {
        CurrentPage++;

        Search(CurrentPage);

        if (GV.Rows.Count.Equals(0))
        {
            btnPre.Enabled = true;
            btnNext.Enabled = false;
        }
        else
        {
            btnPre.Enabled = true;
            btnNext.Enabled = true;
        }
        btnFirst.Enabled = true;
    }
}